--    Author    : ChenErHao
--    Name      : ADM.CUBE_DEPB.HQL
--    Functions :  存款立方体
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-09  ChenErHao       1.CREATE THE PROCEDURE
--


INSERT OVERWRITE TABLE ADM.CUBE_DEPB PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG)
 SELECT
     FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE                              -- 跑批日期
    ,DATA_ORG_CODE                                                                                    -- 数据机构
    ,CUSTOMER_TYPE                                                                                    -- 客户类型
    ,PRODUCT_CATEGORY                                                                                 -- 产品类别
    ,NVL(DEP_AGREEMENT_SDATE,'9999')                                                                  -- 存款协议起始日
    ,NVL(DEP_AGREEMENT_EDATE,'9999')                                                                  -- 存款协议到期日
    ,RATE_FIXED_FLOAT                                                                                -- 利率是否固定
    ,RATE_LEVEL_QJ                                                                                    --利率区间
    ,CASE WHEN BASE_RATE=0 THEN 9999
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -30 THEN 10
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -10 THEN 20
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 0 THEN 21
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 =0  THEN 30
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 10  THEN 40
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 30  THEN 50
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 50 THEN 60
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 100 THEN 70
       WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 > 100  THEN 80
       ELSE 9999
     END AS RATE_LEVEL_FDQJ                                                                              -- 利率浮动区间 
    ,AMT_INTERVAL_ID                                                                                     -- 金额区间
    ,LOAN_PERIOD_TYPE_ID                                                                                 -- 期限区间
    ,SURPLUS_PERIOD_TYPE_ID                                                                              -- 剩余期限区间
    ,TOM.LEGAL_ORG_AREA                                                                                  -- 法人机构所在地区
    ,BELONG_AREA_CODE AS DATA_AREA_CODE                                                                  -- 数据机构所在地区
    ,SUM(IF(DATA_DATE='#V_DATA_DATE#',STROKE_COUNT,0)) STROKE_COUNT                                       -- 笔数
    ,SUM(IF(DATA_DATE='#V_DATA_DATE#',DEP_BALANCE,0)) DEP_BALANCE                                      -- 本月余额
    ,SUM(IF(DATA_DATE='#V_LAST_1M_END_DATE#',DEP_BALANCE,0)) LM_DEP_BALANCE                            -- 上月余额
    ,SUM(IF(DATA_DATE='#V_LAST_12M_END_DATE#',DEP_BALANCE,0)) LY_DEP_BALANCE                           -- 去年同期余额
    ,SUM(IF(DATA_DATE='#V_LAST_1Y_END_DAY#',DEP_BALANCE,0)) LYM_DEP_BALANCE                            -- 去年末余额
    ,SUM(IF(DATA_DATE='#V_DATA_DATE#',JQ_SURPLUS_PERIOD_TYPE_ID,0)) AS AMT_SURPLUS                       -- 加权剩余期限
    ,SUM(IF(DATA_DATE='#V_DATA_DATE#',LOAN_OCCURRENCE_LEVEL,0)) AS LOAN_OCCURRENCE_LEVEL                  -- 金额加权值
    ,SUM(IF(DATA_DATE='#V_DATA_DATE#',RATE_LEVEL,0)) AS RATE_LEVEL                                        -- 利率
    
    ,DATA_SRC_ORG
 FROM EDW.DEPB_INFO T
  LEFT JOIN DMCODE.T_ORG_MANAGE TOM ON T.DATA_ORG_CODE=TOM.DATA_ORG_ID
WHERE DATA_DATE IN('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#','#V_LAST_1Y_END_DAY#')
GROUP BY
   DATA_ORG_CODE
  ,CUSTOMER_TYPE
  ,PRODUCT_CATEGORY
  ,DEP_AGREEMENT_SDATE
  ,DEP_AGREEMENT_EDATE
  ,RATE_FIXED_FLOAT
  ,RATE_LEVEL_QJ
  ,CASE WHEN  BASE_RATE=0 THEN 9999
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -30 THEN 10
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= -10 THEN 20
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 0 THEN 21
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 =0  THEN 30
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 10  THEN 40
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 30  THEN 50
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 50 THEN 60
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 <= 100 THEN 70
        WHEN (RATE_LEVEL-BASE_RATE)/BASE_RATE*100 > 100  THEN 80
        ELSE 9999
   END
  ,AMT_INTERVAL_ID
  ,LOAN_PERIOD_TYPE_ID
  ,SURPLUS_PERIOD_TYPE_ID
  ,TOM.LEGAL_ORG_AREA
  ,BELONG_AREA_CODE
  ,DATA_SRC_ORG
;
